﻿using System;
using System.ComponentModel;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Data;
using System.IO;
using System.Text;
using Outlook = Microsoft.Office.Interop.Outlook;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Security.Principal;
using Microsoft.Office.Interop;
using ConfigLibrary;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms.ComponentModel;
using Contacts;
using Excel = Microsoft.Office.Interop.Excel;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Security;
using Microsoft.Exchange.WebServices;
using Microsoft.Exchange.WebServices.Data;
using Microsoft.Exchange.WebServices.Authentication;


namespace Contacts
{
    public partial class olconn : contactconn
    {
        public olconn()
        {
            try
            {
                connecttridentspock();
                Console.WriteLine("Connection made to server to load tables");
                loadtables("trident");
                //Console.WriteLine("Trident tables loaded");
                //loadtables("spock");  this will have to be brought in after spock datatable gets loaded
                disconnect();
                Console.WriteLine("Disconnected from server.");
                connectcontacts();
                Console.WriteLine("Reconnected to server.");
                loadtables("categories");
                loadtables("companies");
                loadtables("contacts");
                loadtables("job_roles");
                loadtables("project_contacts");
                loadtables("projects");
                Console.WriteLine("Tables loaded.");
                disconnect();
                Console.WriteLine("Disconnected from server.");
                CheckFolders(contacts);
                FolderView();
            }
            catch (Exception ex)
            {
                string subject = "CheckFolders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void CheckFolders(DataSet contacts)
        {
            try
            {
                DataTable trident = contacts.Tables["trident"];
                DataTable spock = contacts.Tables["spock"];
                DataTable actstrlist = new DataTable();
                actstrlist.Columns.Add("strfolder");
                actstrlist.Columns.Add("action");
                DataTable olstrlist = new DataTable();
                olstrlist.Columns.Add("strfolder");
                olstrlist.Columns.Add("action");
                olstrlist.Columns.Add("id");
                DataTable cats = contacts.Tables["categories"];
                DataTable blank = null; //this needs to be deleted once Walmart folder is deleted.
                object[] dtables = new object[] { spock, trident, cats, blank };

                ExchangeService service = Service.ConnectToService(UserData.CreateUserData("dplummer@rhaaia.com"), new TraceListener());
                Guid propertySetId = new Guid("{00062004-0000-0000-C000-000000000046}");
                FolderView vw = new Microsoft.Exchange.WebServices.Data.FolderView(int.MaxValue);
                FindFoldersResults results = service.FindFolders(WellKnownFolderName.PublicFoldersRoot, vw);
                ContactsFolder contactsfolder = (ContactsFolder)results.Folders[1];
                FolderId id = contactsfolder.Id;
                FolderView fv = new Microsoft.Exchange.WebServices.Data.FolderView(int.MaxValue);
                FindFoldersResults contacts001 = service.FindFolders(id, fv);
                int fldrcount = contacts001.Folders.Count;

                for (int i = 0; i < fldrcount; i++)
                {
                    if (i == 1 || i == 2) //this needs to be removed once spock is up to date and category process gets implemented
                    {
                        ContactsFolder olfldr = (ContactsFolder)contacts001.Folders[i];
                        string fldrname = olfldr.DisplayName;
                        FolderId olfid = olfldr.Id;
                        FolderView olfldrview = new Microsoft.Exchange.WebServices.Data.FolderView(int.MaxValue);
                        FindFoldersResults contfldr = service.FindFolders(olfid, olfldrview);
                        int contfldrcount = contfldr.Folders.Count;
                        outlookfolders(contfldr, contfldrcount, olstrlist);
                        walmartdatabase((DataTable)dtables[i], actstrlist, olstrlist);
                        deleteoutlookprojects(contfldr, olstrlist);
                        addoutlookprojects(contfldr, olfid, actstrlist, service);
                        contfldr = service.FindFolders(olfid, olfldrview);
                        contfldrcount = contfldr.Folders.Count;
                        FillFolders(contacts, contfldr, (DataTable)dtables[i], service);
                        if (fldrname == "2nd Floor Store Contacts" || fldrname == "3rd Floor Store Contacts")
                        { FillSpreadsheet(fldrname, dtables); }
                    }
                }
            }

            catch (System.Exception ex)
            {
                string subject = "CheckFolders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }


        private void outlookfolders(FindFoldersResults findfldrs, int count, DataTable olstrlist)
        {
            //this iterates through the outlook individual contacts folder list
            try
            {
                for (int or = 0; or < count; or++)
                {
                    Console.WriteLine("Row " + or + " added to outlook datatable.");
                    ContactsFolder project = (ContactsFolder)findfldrs.Folders[or];
                    FolderId olfid = project.Id;
                    string fldrname = project.DisplayName;
                    DataRow olstradd = olstrlist.NewRow();
                    olstradd["strfolder"] = fldrname;
                    olstradd["id"] = olfid;
                    olstrlist.Rows.Add(olstradd);
                }
            }
            catch (Exception ex)
            {
                string subject = "Build for Outlook DataTable Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        private void walmartdatabase(DataTable wmtable, DataTable actstrlist, DataTable olstrlist)
        {
            //this creates datatable of the active trident, spock, or category filter ( I think this is how this could be done )
            try
            {

                for (int sr = 0; sr < wmtable.Rows.Count; sr++)
                {
                    Console.WriteLine("Row " + sr + " added to WM datatable.");
                    string str = "" + wmtable.Rows[sr]["city"] + " " + wmtable.Rows[sr]["st"] +
                        " " + wmtable.Rows[sr]["str_num"] + " - " + wmtable.Rows[sr]["str_seq"] + "";
                    str = str.Replace("'", " ");
                    DataRow stradd = actstrlist.NewRow();
                    stradd["strfolder"] = str;
                    actstrlist.Rows.Add(stradd);
                }

                //this iterates through above table and determines adds
                int actrct = actstrlist.Rows.Count;
                int aslr;

                for (aslr = 0; aslr < actrct; aslr++)
                {
                    string strname = (string)actstrlist.Rows[aslr]["strfolder"];                    
                    strname = strname.Replace("'", " ");
                    DataRow[] actstr = olstrlist.Select(string.Format("strfolder = '{0}'", strname));
                    int act = actstr.Length;

                    if (act == 0)
                    {
                        actstrlist.Rows[aslr]["action"] = "A";
                    }
                }

                actstrlist.DefaultView.Sort = actstrlist.Columns["strfolder"].ToString();
                actstrlist = actstrlist.DefaultView.ToTable();

                //this iterates through above table and determines deletes
                int olstrct = olstrlist.Rows.Count;
                int olsl;

                for (olsl = 0; olsl < olstrct; olsl++)
                {
                    string fldrname = (string)olstrlist.Rows[olsl]["strfolder"];
                    fldrname = fldrname.Replace("'", " ");                    
                    DataRow[] olstr = actstrlist.Select(string.Format("strfolder = '{0}'", fldrname));
                    int osl = olstr.Length;

                    if (osl == 0)
                    {
                        olstrlist.Rows[olsl]["action"] = "D";
                    }
                }
            }
            catch (Exception ex)
            {
                string subject = "Build for either Trident, Spock, or Categories DataTable Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        private void deleteoutlookprojects(FindFoldersResults contfldr, DataTable olstrlist)
        {
            //this deletes the projects that are 180 days past GO date in the store contacts folders or deletes categories in the "Contacts" folder
            try
            {

                string d = "D";
                DataRow[] delete = olstrlist.Select(string.Format("action = '{0}'", d));
                int delrc = delete.Length;
                int dr;
                for (dr = 0; dr < delrc; dr++)
                {
                    string name = (string)delete[dr]["strfolder"];
                    name = name.Replace("'", " ");

                    for (int or = 1; or < contfldr.Folders.Count; or++)
                    {
                        ContactsFolder project = (ContactsFolder)contfldr.Folders[or];

                        if (project.DisplayName == name)
                        {
                            if (project.DisplayName == "001 Contacts" || project.DisplayName == "2nd Floor Contacts" || project.DisplayName == "3rd Floor Contacts" ||
                                project.DisplayName == "Contacts" || project.DisplayName == "Walmart")
                            {
                                mailman.sendmail("CAUTION! CAUTION!", "An incorrect folder was about to be deleted!");
                                return;
                            }
                            else
                            {
                                project.Delete(DeleteMode.HardDelete);
                                Console.WriteLine("Project folder " + name + " is being deleted from Outlook.");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string subject = "Delete Outlook folders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        private void addoutlookprojects(FindFoldersResults contfldr, FolderId olfid, DataTable actstrlist, ExchangeService service)
        {
            //this adds projects to stores contacts folders or adds categories to "Contacts" folder
            try
            {

                string a = "A";
                DataRow[] adder = actstrlist.Select(string.Format("action = '{0}'", a));
                int addrc = adder.Length;
                int adr;
                //contact = new Contact(service);
                //FolderView projectview = new Microsoft.Exchange.WebServices.Data.FolderView(int.MaxValue);
                //projectviewprops = new PropertySet(StoreNumber, StoreSeq, StoreCity, StoreSt, JobRole,
                //    ContactSchema.JobTitle, ContactSchema.CompanyName, ContactSchema.BusinessAddressStreet, Suite, ContactSchema.BusinessAddressCity,
                //    ContactSchema.BusinessAddressState, ContactSchema.BusinessAddressPostalCode, ContactSchema.BusinessPhone, ContactSchema.BusinessPhone2,
                //    ContactSchema.BusinessFax, ContactSchema.MobilePhone, ContactSchema.EmailAddress1, ContactSchema.Categories);
                //projectview.PropertySet = projectviewprops;

                for (adr = 0; adr < addrc; adr++)
                {
                    string name = (string)adder[adr]["strfolder"];
                    ContactsFolder project = new ContactsFolder(service);
                    project.DisplayName = name;
                    project.Save(olfid);
                    FolderId id = project.Id;
                    project = ContactsFolder.Bind(service, id);
                    project.DisplayName = name;
                    Console.WriteLine("Project folder " + name + " is being added to Outlook.");
                    project.Update();
                    name = null;
                }
            }
            catch (Exception ex)
            {
                string subject = "Add to Outlook folders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        //this fills the outlook folder with the current list of contacts based on this morning's trident dump

        public void FillFolders(DataSet contacts, FindFoldersResults contfldr, DataTable wmdata, ExchangeService service)
        {
            try
            {
                int count = contfldr.Folders.Count;
                DataTable proj = contacts.Tables["projects"];
                DataTable projcont = contacts.Tables["project_contacts"];

                for (int wmr = 0; wmr < wmdata.Rows.Count; wmr++)
                {
                    string wmids = (string)wmdata.Rows[wmr]["id"];
                    int wmid = int.Parse(wmids);
                    string str = "" + wmdata.Rows[wmr]["city"] + " " + wmdata.Rows[wmr]["st"] +
                            " " + wmdata.Rows[wmr]["str_num"] + " - " + wmdata.Rows[wmr]["str_seq"] + "";
                    Console.WriteLine("Project " + str + " is being updated.");

                    for (int or = 0; or < count; or++)
                    {
                        ContactsFolder project = (ContactsFolder)contfldr.Folders[or];
                        FolderSchema fs = (FolderSchema)project.Schema;
                        FolderId olfid = project.Id;
                        ItemView iv = new ItemView(int.MaxValue);
                        //iv.PropertySet = projectviewprops;
                        string fldrname = project.DisplayName;                        
                        FindItemsResults<Microsoft.Exchange.WebServices.Data.Item> items = service.FindItems(olfid, iv);
                        int dcc = items.Items.Count;

                        if (fldrname == str)
                        {
                            if (dcc > 0)
                            {
                                for (int dc = dcc - 1; dc >= 0; dc--)
                                {
                                    items.Items[dc].Delete(DeleteMode.HardDelete);
                                }
                            }
                            DataRow[] strdr = proj.Select(string.Format("trident_id = '{0}'", wmid));
                            int strid = (int)strdr[0]["str_id"]; ;
                            DataRow[] filter = projcont.Select(string.Format("str_id = {0}", strid));

                            items = service.FindItems(olfid, iv);

                            for (int c = 0; c < filter.Length; c++)
                            {
                                DataRow jr = contacts.Tables["job_roles"].Rows.Find(filter[c]["job_role_id"]);
                                DataRow cont = contacts.Tables["contacts"].Rows.Find(filter[c]["contact_id"]);
                                DataRow comp = contacts.Tables["companies"].Rows.Find(cont["company_id"]);
                                Contact contact = new Contact(service);
                                contact.ItemClass = "IPM.Contact.ProjectContactForm";
                                contact.OfficeLocation = "" + strdr[0].ItemArray[2].ToString() + "-" + strdr[0].ItemArray[3].ToString() +
                                    " " + strdr[0].ItemArray[4].ToString() + ", " + strdr[0].ItemArray[5].ToString() + "";
                                contact.Profession = jr["job_role"].ToString();
                                contact.GivenName = cont["first_name"].ToString();
                                if (!String.IsNullOrEmpty(cont["middle_name"].ToString()))
                                { contact.MiddleName = cont["middle_name"].ToString(); }
                                contact.Surname = cont["last_name"].ToString();
                                if (!String.IsNullOrEmpty(cont["job_title"].ToString()))
                                { contact.JobTitle = cont["job_title"].ToString(); }
                                contact.CompanyName = comp["company_name"].ToString();
                                PhysicalAddressEntry pae = new PhysicalAddressEntry();
                                if (comp["suite"].ToString() == "")
                                { pae.Street = comp["street"].ToString(); }
                                else
                                { pae.Street = "" + comp["street"].ToString() + ",  " + comp["suite"].ToString(); }
                                pae.City = comp["city"].ToString();
                                pae.State = comp["state"].ToString();
                                pae.PostalCode = comp["zip"].ToString();
                                contact.PhysicalAddresses[PhysicalAddressKey.Business] = pae;
                                if (!String.IsNullOrEmpty(cont["direct_phone"].ToString()))
                                { contact.PhoneNumbers[PhoneNumberKey.BusinessPhone] = cont["direct_phone"].ToString(); }
                                contact.PhoneNumbers[PhoneNumberKey.BusinessPhone2] = comp["main_phone"].ToString();
                                if (!String.IsNullOrEmpty(comp["fax"].ToString()))
                                { contact.PhoneNumbers[PhoneNumberKey.BusinessFax] = comp["fax"].ToString(); }
                                if (!String.IsNullOrEmpty(cont["mobile_phone"].ToString()))
                                { contact.PhoneNumbers[PhoneNumberKey.MobilePhone] = cont["mobile_phone"].ToString(); }
                                if (!String.IsNullOrEmpty(cont["email"].ToString()))
                                { contact.EmailAddresses[EmailAddressKey.EmailAddress1] = cont["email"].ToString(); }
                                DataRow cat = contacts.Tables["categories"].Rows.Find(comp["category_id"]);
                                contact.Categories.Add(cat["category"].ToString());
                                contact.Save(olfid);
                                ItemId iid = contact.Id;
                                contact = Contact.Bind(service, iid);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string subject = "CheckFolders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void FillSpreadsheet(string foldername, object[] dtables)
        {
            try
            {
                uint h;
                int col;
                DataTable wmdata = null;
                DataTable proj = contacts.Tables["projects"];
                DataTable projcont = contacts.Tables["project_contacts"];
                string path = @"u:\Reports\Project Contacts\" + foldername + "";
                string[] listofcontacts = Directory.GetFiles(path);
                //this deletes the excel files in the folder
                //int dc = dcc - 1; dc >= 0; dc--
                for (int f = listofcontacts.Length - 1; f >= 0; f--)
                {
                    File.Delete(listofcontacts[f]);
                    Console.WriteLine("File " + listofcontacts[f] + " is being deleted.");
                }

                if (foldername == "2nd Floor Store Contacts")
                {
                    wmdata = (DataTable)dtables[0];
                }
                else if (foldername == "3rd Floor Store Contacts")
                {
                    wmdata = (DataTable)dtables[1];
                }

                for (int wmr = 0; wmr < wmdata.Rows.Count; wmr++)
                {
                    string wmids = (string)wmdata.Rows[wmr]["id"];
                    int wmid = int.Parse(wmids);
                    DataRow[] strdr = proj.Select(string.Format("trident_id = '{0}'", wmid));
                    int strid = (int)strdr[0]["str_id"]; ;
                    DataRow[] filter = projcont.Select(string.Format("str_id = {0}", strid));

                    DataTable strcontacts = new DataTable();
                    string[] headername = new string[] {"Str Num", "Seq", "Store City", "Str St", "Job Role", "Full Name", "Company", "Address", "Suite", 
                    "City", "St", "Zip", "Direct", "Main", "Fax", "Mobile", "Email", "Category"};
                    string[] alpha = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R" };

                    for (int a = 0; a < headername.Length; a++)
                    { strcontacts.Columns.Add(headername[a]); }

                    for (int f = 0; f < filter.Length; f++)
                    {
                        DataRow pc = strcontacts.NewRow();
                        DataRow jr = contacts.Tables["job_roles"].Rows.Find(filter[f].ItemArray[2]);
                        DataRow cont = contacts.Tables["contacts"].Rows.Find(filter[f].ItemArray[3]);
                        string name;
                        if (String.IsNullOrEmpty(cont["middle_name"].ToString()))
                        { name = cont["first_name"].ToString() + " " + cont["last_name"].ToString(); }
                        else
                        { name = cont["first_name"].ToString() + " " + cont["middle_name"].ToString() + " " + cont["last_name"].ToString(); }
                        DataRow comp = contacts.Tables["companies"].Rows.Find(cont["company_id"]);
                        DataRow cat = contacts.Tables["categories"].Rows.Find(comp["category_id"]);
                        List<string> fields = new List<string> { strdr[0]["str_num"].ToString(), strdr[0]["str_seq"].ToString(), strdr[0]["str_city"].ToString(), strdr[0]["str_st"].ToString(),
                    jr["job_role"].ToString(), name, comp["company_name"].ToString(), comp["street"].ToString(), comp["suite"].ToString(), comp["city"].ToString(), comp["state"].ToString(),
                    comp["zip"].ToString(), cont["direct_phone"].ToString(), comp["main_phone"].ToString(), comp["fax"].ToString(), cont["mobile_phone"].ToString(),
                    cont["email"].ToString(), cat["category"].ToString() };
                        for (int d = 0; d < strcontacts.Columns.Count; d++)
                        { pc[headername[d]] = fields[d]; }
                        strcontacts.Rows.Add(pc);
                    }
                    strcontacts.DefaultView.Sort = strcontacts.Columns["Full Name"].ToString();
                    strcontacts = strcontacts.DefaultView.ToTable();
                    strcontacts.DefaultView.Sort = strcontacts.Columns["Job Role"].ToString();
                    strcontacts = strcontacts.DefaultView.ToTable();
                    string city = strdr[0].ItemArray[4].ToString();
                    if (city.Contains("\\"))
                    {
                        string[] citysplit;
                        citysplit = city.Split(new Char[] { '\\' });
                        city = citysplit[0] + citysplit[1];
                    }

                    path = (@"u:\Reports\Project Contacts\3rd Floor Store Contacts\ " + city + " " + strdr[0].ItemArray[5].ToString() + " " +
                     strdr[0].ItemArray[2].ToString() + "-" + strdr[0].ItemArray[3].ToString() + ".xlsx");
                    SpreadsheetDocument xl = null;
                    //creates excel file
                    xl = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook, true);
                    //creates the workbookpart and worksheetpart
                    WorkbookPart workbook = xl.AddWorkbookPart();
                    WorksheetPart worksheet = workbook.AddNewPart<WorksheetPart>("rId1");
                    SharedStringTablePart sharedstring = xl.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    SharedStringTable sst = new SharedStringTable();
                    sharedstring.SharedStringTable = sst;
                    //create the workbook parts
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion() { ApplicationName = "Microsoft Office Excel" };
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet() { Id = xl.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Contacts" }; //xl.WorkbookPart.GetIdOfPart(worksheet)
                    sheets.Append(sheet);
                    wb.Append(fv);
                    wb.Append(sheets);
                    //creates the styles
                    WorkbookStylesPart styles = xl.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                    styles.Stylesheet = GenerateStyleSheet();
                    styles.Stylesheet.Save();
                    //creates the worksheet parts               
                    Worksheet ws = new Worksheet();
                    HeaderFooter hf = new HeaderFooter(
                        new OddHeader()
                        {
                            Text = "Contacts for " + strdr[0].ItemArray[4].ToString() + ", " +
                                strdr[0].ItemArray[5].ToString() + " - " + strdr[0].ItemArray[2].ToString() + "-" +
                                strdr[0].ItemArray[3].ToString()
                        },
                        new OddFooter()
                        ) { AlignWithMargins = false, DifferentFirst = false, DifferentOddEven = false };
                    PageSetup pagesetup = new PageSetup() { FitToWidth = 1, Orientation = OrientationValues.Landscape, PaperSize = 3 };
                    SheetProperties sp = new SheetProperties();
                    PageSetupProperties pagesetupprop = new PageSetupProperties() { FitToPage = true };
                    sp.Append(pagesetupprop);
                    ws.Append(sp);
                    Columns columns = new Columns(
                        new Column() { Min = 1, Max = 1, Width = 10, CustomWidth = true },
                        new Column() { Min = 2, Max = 2, Width = 5, CustomWidth = true },
                        new Column() { Min = 3, Max = 3, Width = 20, CustomWidth = true },
                        new Column() { Min = 4, Max = 4, Width = 6, CustomWidth = true },
                        new Column() { Min = 5, Max = 5, Width = 40, CustomWidth = true },
                        new Column() { Min = 6, Max = 6, Width = 25, CustomWidth = true },
                        new Column() { Min = 7, Max = 7, Width = 35, CustomWidth = true },
                        new Column() { Min = 8, Max = 8, Width = 40, CustomWidth = true },
                        new Column() { Min = 9, Max = 9, Width = 14, CustomWidth = true },
                        new Column() { Min = 10, Max = 10, Width = 20, CustomWidth = true },
                        new Column() { Min = 11, Max = 11, Width = 5, CustomWidth = true },
                        new Column() { Min = 12, Max = 16, Width = 18, CustomWidth = true },
                        new Column() { Min = 17, Max = 17, Width = 40, CustomWidth = true },
                        new Column() { Min = 18, Max = 18, Width = 30, CustomWidth = true }
                        );
                    ws.Append(columns);

                    TableColumns tablecolumns = new TableColumns() { Count = (UInt32)(headername.Length) };
                    for (h = 0; h < headername.Length; h++)
                    { GenerateTableColumn(h + 1, headername[h].ToString(), tablecolumns); }

                    SheetData data = new SheetData();

                    Row headerrow = new Row { RowIndex = (UInt32)1 };
                    for (col = 0; col < strcontacts.Columns.Count; col++)
                    {
                        Cell cell = new Cell() { CellReference = alpha[col] + "1", DataType = CellValues.String, CellValue = new CellValue(headername[col].ToString()), StyleIndex = 3 };
                        headerrow.Append(cell);
                    }
                    data.Append(headerrow);

                    for (int r = 0; r < strcontacts.Rows.Count; r++)
                    {
                        int ri = r + 2;
                        Row row = new Row { RowIndex = (UInt32)ri };

                        for (col = 0; col < strcontacts.Columns.Count; col++)
                        {
                            Cell cell = null;
                            if (col == 0 || col == 1 || col == 3 || col == 10 || col == 11 || col == 12 || col == 13 || col == 14 || col == 15)
                            {
                                cell = new Cell() { CellReference = alpha[col] + ri, DataType = CellValues.String, CellValue = new CellValue(strcontacts.Rows[r][headername[col].ToString()].ToString()), StyleIndex = 1 };
                                row.Append(cell);
                            }
                            else
                            {
                                cell = new Cell() { CellReference = alpha[col] + ri, DataType = CellValues.String, CellValue = new CellValue(strcontacts.Rows[r][headername[col].ToString()].ToString()), StyleIndex = 2 };
                                row.Append(cell);
                            }
                        }
                        data.Append(row);
                    }
                    ws.Append(data);

                    TableDefinitionPart tdp = worksheet.AddNewPart<TableDefinitionPart>("rId1");
                    Table table = new Table()
                    {
                        Id = (UInt32)1U,
                        HeaderRowCount = 1,
                        DisplayName = "Contacts",
                        Name = "Contacts",
                        Reference = "A1:R" + (UInt32)(filter.Length + 1),
                        BorderFormatId = 0,
                        TotalsRowShown = false
                    };
                    table.Append(tablecolumns);
                    TableStyleInfo tablestyle = new TableStyleInfo() { Name = "TableStyleLight1", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false, };
                    table.Append(tablestyle);
                    TableParts tps = new TableParts() { Count = (UInt32)1U };
                    TablePart tp = new TablePart() { Id = "rId1" };
                    tps.Append(tp);
                    tdp.Table = table;
                    ws.Append(pagesetup);
                    ws.Append(hf);
                    ws.Append(tps);
                    worksheet.Worksheet = ws;
                    ws.Save();
                    xl.WorkbookPart.Workbook = wb;
                    wb.Save();
                    xl.Close();
                }
            }
            catch (Exception ex)
            {
                string subject = "CheckFolders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        private Stylesheet GenerateStyleSheet()
        {

            return new Stylesheet(

                new Fonts(new DocumentFormat.OpenXml.Spreadsheet.Font(
                //index 0 = default font
                    new FontSize() { Val = 8 },
                    new FontName() { Val = StringValue.FromString("Calibri") })
                //new DocumentFormat.OpenXml.Spreadsheet.Color() {Auto = true })
                    ),

                new Fills(new Fill(
                //index 0 = default fill
                        new PatternFill() { PatternType = PatternValues.None }),
                    new Fill(
                //index 1 = default fill of gray 125 required
                        new PatternFill() { PatternType = PatternValues.Gray125 })),

                new Borders(new Border(
                //index 0 = default border
                    new LeftBorder(),
                    new RightBorder(),
                    new TopBorder(),
                    new BottomBorder(),
                    new DiagonalBorder()),
                //index 1 = top, bottom, left, right borders thin
                    new Border(
                        new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new DiagonalBorder()),
                //index 2 = top, bottom, left, right borders thin
                    new Border(
                        new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                        new DiagonalBorder())),

                new CellFormats(
                //index = 0
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, ApplyFont = true },

                    //index 1 = regular font + thin borders + center alignment
                    new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
                        ) { FontId = 0, FillId = 0, BorderId = 1, ApplyFont = true, ApplyBorder = true },

                    //index 2 = regular font + thin borders + left alignment
                    new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center }
                        ) { FontId = 0, FillId = 0, BorderId = 1, ApplyFont = true, ApplyBorder = true },

                    //index 3 = regular font + thin borders + left alignment
                    new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
                        ) { FontId = 0, FillId = 0, BorderId = 2, ApplyFont = true, ApplyBorder = true }


                        ),

                    new DifferentialFormats(
                        new DifferentialFormat(
                            new Border(
                                new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                                new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                                new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                                new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                                new DiagonalBorder())),

                        new DifferentialFormat(
                //new DocumentFormat.OpenXml.Spreadsheet.Font(
                //new Bold(),
                //new FontSize() { Val = 8 },
                //new FontName() { Val = "Calibri" }
                //),
                            new Border(
                                new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                                new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                                new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                                new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
                                new DiagonalBorder())
                //new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }

                        )) { Count = (uint)2 },
                    new TableStyles() { Count = (UInt32)0, DefaultTableStyle = "TableStyleLight1" }
                );
        }

        public void GenerateTableColumn(uint i, string name, TableColumns tablecolumns)
        {
            try
            {
                //Cell headercell = new Cell(new InlineString(new Text { Text = name.ToString() }));
                TableColumn tablecolumn = new TableColumn() { Id = i, Name = name };
                tablecolumns.Append(tablecolumn);
            }
            catch (Exception ex)
            {
                string subject = "CheckFolders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        private static int InsertSharedStringItem(string text, SharedStringTable sharedtable)
        {            
            int ssi = 0;
            foreach (SharedStringItem item in sharedtable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return ssi;
                }
                ssi++;
            }
            sharedtable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            return ssi;
        }

        public void FolderView()
        {
            try
            {
                Outlook.Application ol = new Outlook.Application();
                Outlook.MAPIFolder fldr = ol.GetNamespace("MAPI").GetDefaultFolder(Outlook.OlDefaultFolders.olPublicFoldersAllPublicFolders).Folders["001 Contacts"];
                Outlook.Folders contactfldrs = fldr.Folders;
                int olfldrcount = contactfldrs.Count;
                Outlook.MAPIFolder contactfldr = contactfldrs[2];
                Outlook.Folders strfldrs = contactfldr.Folders;
                Outlook.MAPIFolder strfldr;
                Outlook.View view;
                int strfldrcount = strfldrs.Count;
                int contvcount = fldr.Views.Count;
                for (int vc = 1; vc < contvcount + 1; vc++)
                {
                    string vw = fldr.Views[vc].Name;
                }
                Outlook.View v = fldr.Views[2];

                for (int f = 1; f < strfldrcount + 1; f++)
                {
                    int vcount = strfldrs[f].Views.Count;

                    int b;
                    for (b = 1; b < vcount + 1; b++)
                    {
                        if (strfldrs[f].Views[b].Name == "ProjectContactsView1")
                        {
                            strfldrs[f].Views[b].Apply();
                            break;
                        }
                    }

                    //if (b == vcount + 1)
                    //{
                    //    //v.Copy("ProjectContactsView", Outlook.OlViewSaveOption.olViewSaveOptionAllFoldersOfType);
                    //    //v.Copy("ProjectContactsView1", Outlook.OlViewSaveOption.olViewSaveOptionThisFolderEveryone);
                    //    //v.Save();
                    //}
                    //strfldr = strfldrs[f];

                    //strfldr.Views["ProjectContactsView1"].Apply();
                }
            }
            catch (Exception ex)
            {
                string subject = "CheckFolders Error";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }
    }
}

